﻿Imports System.Data.SqlClient

Public Class FrmSuaHoSoBienDong
    Private Sub NapLoaiDat1()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select kyhieu, tenld from LOAIDAT", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbLD.DisplayMember = "kyhieu"
        CmbLD.ValueMember = "kyhieu"
        CmbLD.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDonViHuyen()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbHuyen.DisplayMember = "T_huyen"
        CmbHuyen.ValueMember = "maH"
        CmbHuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiTaiLieu()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTaiLieu", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbLoaiTaiLieu.DisplayMember = "TenLoaiTaiLieu"
        CmbLoaiTaiLieu.ValueMember = "maLoaiTaiLieuID"
        CmbLoaiTaiLieu.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapPhong()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMPhong", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        cmbPhong.DisplayMember = "Tenphong"
        cmbPhong.ValueMember = "maPhongID"
        cmbPhong.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapNgan()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMNgan", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbNgan.DisplayMember = "TenNgan"
        CmbNgan.ValueMember = "maNganID"
        CmbNgan.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKe()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKe", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbKe.DisplayMember = "TenKe"
        CmbKe.ValueMember = "maKeID"
        CmbKe.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKho()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKho", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbKho.DisplayMember = "TenKho"
        CmbKho.ValueMember = "maKhoID"
        CmbKho.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapTinhTrang()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTinhTrang", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbTinhTrang.DisplayMember = "TenTinhTrang"
        CmbTinhTrang.ValueMember = "maTinhTrangID"
        CmbTinhTrang.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub CmbHuyen_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CmbHuyen.SelectedIndexChanged
        ' load ten xa
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TenXa where mahuyen = '" & CmbHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbXa.DisplayMember = "TenXa"
        CmbXa.ValueMember = "maxa"
        CmbXa.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapVaoTexBox()
        TextSoHoSo.DataBindings.Clear()
        TextSoHoSo.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "SoHoSo")
        TextTenChu.DataBindings.Clear()
        TextTenChu.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "tenchu")
        TextDiaChi.DataBindings.Clear()
        TextDiaChi.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "diachi")
        cmbPhong.DataBindings.Clear()
        cmbPhong.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "Tenphong")
        CmbNgan.DataBindings.Clear()
        CmbNgan.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "TenNgan")
        CmbKe.DataBindings.Clear()
        CmbKe.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "TenKe")
        CmbKho.DataBindings.Clear()
        CmbKho.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "TenKho")
        CmbTinhTrang.DataBindings.Clear()
        CmbTinhTrang.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "TenTinhTrang")
        TextMaBienDong.DataBindings.Clear()
        TextMaBienDong.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "MaBienDong")
        cbLoaiBienDong.DataBindings.Clear()
        cbLoaiBienDong.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "TenLoaiBD")
        CmbXa.DataBindings.Clear()
        CmbXa.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "tenxa")
        CmbHuyen.DataBindings.Clear()
        CmbHuyen.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "T_huyen")
        NapDataGridViewThuaDat()
    End Sub
    Private Sub Lamtuoi()
        TextSoHoSo.Text = ""
        TextTenChu.Text = ""
        TextDiaChi.Text = ""
        TextthuaID.Text = ""
    End Sub
    Private Sub NapVaoTexBoxThuaDat()
        'TextSoHoSo.DataBindings.Clear()
        'TextSoHoSo.DataBindings.Add("text", DataGridViewThuaDat.DataSource, "sohoso")
        TextthuaID.DataBindings.Clear()
        TextthuaID.DataBindings.Add("text", DataGridViewThuaDat.DataSource, "thuaid")
        TextSoThua.DataBindings.Clear()
        TextSoThua.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "thuadat")
        TextToBD.DataBindings.Clear()
        TextToBD.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "tobando")
        TextDienTich.DataBindings.Clear()
        TextDienTich.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Dientich")
        CmbLD.DataBindings.Clear()
        CmbLD.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Loaidat")
        CmbLoaiTaiLieu.DataBindings.Clear()
        CmbLoaiTaiLieu.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "TenLoaitailieu")
    End Sub
    Private Sub NapLoaiBienDong()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from LOAIBD", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        cbLoaiBienDong.DisplayMember = "TenLoaiBD"
        cbLoaiBienDong.ValueMember = "maBD"
        cbLoaiBienDong.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub Update_TenChu()
        Try
            Dim Sql As String = ""
            Sql = " Update TENCHUBIENDONG Set xaID = @xaID, HuyenID = @huyenID, tenchu = @tenchu, TinhTrangID = @tinhtrangID, Diachi = @diachi," &
                " phongID = @phongID, nganID = @nganID, keID = @keID, khoID = @khoID, MaBienDong = @mabiendong, LoaiBienDongID = @loaiBDID where SoHoSo = @sohoso"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            cmd.Parameters.AddWithValue("@xaID", CmbXa.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@HuyenID", CmbHuyen.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@tenchu", TextTenChu.Text)
            cmd.Parameters.AddWithValue("@tinhtrangID", CmbTinhTrang.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@diachi", TextDiaChi.Text)
            cmd.Parameters.AddWithValue("@phongID", cmbPhong.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@nganID", CmbNgan.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@keID", CmbKe.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@khoID", CmbKho.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@mabiendong", TextMaBienDong.Text)
            cmd.Parameters.AddWithValue("@loaiBDID", cbLoaiBienDong.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@sohoso", TextSoHoSo.Text)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
            MsgBox("Sửa tên chủ thành công!")
            NapDataGridViewtenchu()
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub
    Private Sub Update_ThuaDat()
        Try
            Dim Sql As String = ""
            Sql = " Update THUADATBIENDONG Set ThuaDat = @thuadat, ToBanDo = @tobando, DienTich = @dientich, LoaiDat = @loaidat, LoaiTaiLieu = @loaitailieu, XaID = @xaID, HuyenID = @huyenID where thuaID = @thuaID"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.Parameters.AddWithValue("@thuadat", TextSoThua.Text)
            cmd.Parameters.AddWithValue("@tobando", TextToBD.Text)
            cmd.Parameters.AddWithValue("@dientich", TextDienTich.Text)
            cmd.Parameters.AddWithValue("@loaidat", CmbLD.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@loaitailieu", CmbLoaiTaiLieu.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@xaID", CmbXa.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@huyenID", CmbHuyen.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@thuaID", TextthuaID.Text)
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
            MsgBox("Sửa thửa đất thành công!")
            NapDataGridViewThuaDat()
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub
    Private Sub NapDataGridView()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select tc.SohoSo, tc.TenChu, tc.DiaChi, tt.tentinhtrang, p.tenphong , ng.tenngan, k.tenke, kh.tenkho, tc.MaBienDong, lbd.TenLoaiBD, x.tenxa, h.T_huyen  " &
                                " From TENCHUBIENDONG tc, DMTinhTrang tt, DMPhong p, DMNgan ng, DmKe k, DmKho kh, tenxa x, tenhuyen h, LoaiBD lbd" &
                                " where TinhtrangID= tt.matinhtrangid " &
                                " and tc.phongid=p.maphongid " &
                                " and tc.nganid = ng.manganid " &
                                " and tc.keid = k.makeid " &
                                " and tc.khoid = kh.makhoid " &
                                " and tc.xaid = x.maxa " &
                                " and xaid = '" & CmbXa.SelectedValue.ToString & "' " &
                                " and tc.huyenid = h.mah " &
                                " and huyenid = '" & CmbHuyen.SelectedValue.ToString & "' " &
                                " and tc.LoaiBienDongID = lbd.MaBD", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewTenChu.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDataGridViewtenchu()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select SohoSo, TenChu, DiaChi,tt.tentinhtrang, p.tenphong , ng.tenngan, k.tenke, kh.tenkho, MaBienDong, tenxa, T_huyen  " &
                                " From TENCHUBIENDONG, DMTinhTrang tt, DMPhong p, DMNgan ng, DmKe k, DmKho kh, tenxa x, tenhuyen h" &
                                " where TinhtrangID= tt.matinhtrangid " &
                                " and phongid=p.maphongid " &
                                " and nganid = ng.manganid " &
                                " and keid = k.makeid " &
                                " and khoid = kh.makhoid " &
                                " and xaid = x.maxa " &
                                " and xaid = '" & CmbXa.SelectedValue.ToString & "' " &
                                " and huyenid = h.mah " &
                                " and huyenid = '" & CmbHuyen.SelectedValue.ToString & "' " &
                                " and TenChu like N'%" & TextTenChu.Text & "%'", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewTenChu.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDataGridViewThuaDat()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select thua.thuaid, thua.sohoso, thua.thuadat, thua.tobando, thua.dientich, thua.loaidat, " &
                                " tl.tenloaitailieu " &
                                " From  THUADATBIENDONG thua,  DMTailieu tl  " &
                                 " WHERE thua.sohoso = '" & TextSoHoSo.Text & "' " &
                                 " and thua.loaitailieu = tl.maloaitailieuID ", ob_cnn)

        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewThuaDat.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub TimThuaDat()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select thua.thuaid, thua.sohoso, thua.thuadat, thua.tobando, thua.dientich, thua.loaidat, " &
                                " tl.tenloaitailieu, tx.tenxa, th.T_huyen," &
                                " p.tenphong, ng.tenngan, k.tenke, kh.tenkho, thua.MaBienDong, ten.tenchu " &
                                " From TENCHUBIENDONG ten, THUADATBIENDONG thua,  DMTailieu tl,  " &
                                " DMPhong p, DMNgan ng, DMKe k, DMKho kh,  tenxa tx, tenhuyen th  " &
                                " WHERE  thua.tobando = '" & TextToBD.Text & "' and thua.thuadat = '" & TextSoThua.Text & "' " &
                                " and thua.SoHoSo = ten.SoHoSo " &
                                " and thua.loaitailieu = tl.maloaitailieuID " &
                                " and thua.xaid = tx.maxa " &
                                " and thua.huyenid = th.mah " &
                                " and thua.phongID = p.maphongid " &
                                " and thua.nganid = ng.manganid " &
                                " and thua.keid = k.makeid " &
                                " and thua.khoid = kh.makhoid ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewThuaDat.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub QL_HoSoSuaTen()
        Try
            Dim tennhap As String
            tennhap = ModuleTenDangNhap.m_tennhap
            Dim NoidungSua As String = " Sửa thông tin chủ sử dụng"
            Dim Sql As String = ""
            Sql = " insert into QL_SuaHoSoBienDong(HoSoBienDong, Nguoisua, Ngaysua, NoiDungSua ) values (@SoHoSoBienDong, @NguoiSua, @NgaySua, @NoiDungSua)"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.Parameters.AddWithValue("@SoHoSoBienDong", TextSoHoSo.Text)
            cmd.Parameters.AddWithValue("@NguoiSua", tennhap)
            cmd.Parameters.AddWithValue("@NgaySua", DateTime.Now)
            cmd.Parameters.AddWithValue("@NoiDungSua", NoidungSua)
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub QL_HoSoSuaThua()
        Try
            Dim tennhap As String
            tennhap = ModuleTenDangNhap.m_tennhap
            Dim NoidungSua As String = " Sửa thông tin thửa đất"
            Dim Sql As String = ""
            Sql = " insert into QL_SuaHoSoBienDong(HoSoBienDong, Nguoisua, Ngaysua, NoiDungSua) values (@SoHoSoBienDong, @NguoiSua, @NgaySua, @NoiDungSua)"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.Parameters.AddWithValue("@SoHoSoBienDong", TextSoHoSo.Text)
            cmd.Parameters.AddWithValue("@NguoiSua", tennhap)
            cmd.Parameters.AddWithValue("@NgaySua", DateTime.Now)
            cmd.Parameters.AddWithValue("@NoiDungSua", NoidungSua)
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub QL_XoaHoSoBienDong()
        Try
            Dim tennhap As String
            tennhap = ModuleTenDangNhap.m_tennhap
            Dim Sql As String = ""
            Sql = " insert into QL_XoaHoSoBienDong(HoSoBienDong, Nguoixoa, Ngayxoa) values(@HoSoBienDong, @NguoiXoa, @NgayXoa)"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.Parameters.AddWithValue("@HoSoBienDong", TextSoHoSo.Text)
            cmd.Parameters.AddWithValue("@NguoiXoa", tennhap)
            cmd.Parameters.AddWithValue("@NgayXoa", DateTime.Now)
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub XoaDuLieuTenChu()
        Dim sql As String = ""
        sql = "Delete from TENCHUBIENDONG where SoHoSo = '" & TextSoHoSo.Text & "'"
        Dim cmd As New SqlCommand(sql, ob_cnn)
        ob_cnn.Open()
        cmd.ExecuteNonQuery()
        ob_cnn.Close()

    End Sub
    Private Sub XoaDuLieuThuaDat()
        Dim sql As String
        sql = "Delete from THUADATBIENDONG where SoHoSo = '" & TextSoHoSo.Text & "'"
        Dim cmd As New SqlCommand(sql, ob_cnn)
        ob_cnn.Open()
        cmd.ExecuteNonQuery()
        ob_cnn.Close()

    End Sub
    Private Sub XoaDuLieuMucLucBienDong()
        Dim sql As String
        sql = "Delete from MUCLUCBIENDONG where SoHoSo = '" & TextSoHoSo.Text & "'"
        Dim cmd As New SqlCommand(sql, ob_cnn)
        ob_cnn.Open()
        cmd.ExecuteNonQuery()
        ob_cnn.Close()

    End Sub
    Private Sub ChuyenTenChu()
        Try
            Dim Sql As String = ""
            Sql = "insert into TENCHUBIENDONGXOA(SoHoSo,TenChu, DiaChi, XaID, HuyenID, TinhTrangID) " &
                "values(@SoHoSo, @TenChu, @DiaChi, @XaID, @HuyenID, @TinhTrangID)"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            cmd.Parameters.AddWithValue("@SoHoSo", TextSoHoSo.Text)
            cmd.Parameters.AddWithValue("@TenChu", TextTenChu.Text)
            cmd.Parameters.AddWithValue("@DiaChi", TextDiaChi.Text)
            cmd.Parameters.AddWithValue("@XaID", CmbXa.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@HuyenID", CmbHuyen.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@TinhTrangID", CmbTinhTrang.SelectedValue.ToString())
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
            QL_XoaHoSoBienDong()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub Chuyenthuadat()
        Try
            Dim Sql As String = ""
            Sql = " insert into THUADATBIENDONGXOA(SoHoSo, ThuaDat, ToBanDo, DienTich, LoaiDat, LoaiTaiLieu, " &
                " XaID, HuyenID, PhongID, NganID, KeID, KhoID, MaBienDong) values (@SoHoSo, @ThuaDat, @ToBanDo, @DienTich, @LoaiDat, @LoaiTaiLieu, @XaID, @HuyenID, @PhongID, @NganID, @KeID, @KhoID, @MaBienDong)"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            cmd.Parameters.AddWithValue("@SoHoSo", TextSoHoSo.Text)
            cmd.Parameters.AddWithValue("@ThuaDat", TextSoThua.Text)
            cmd.Parameters.AddWithValue("@ToBanDo", TextToBD.Text)
            cmd.Parameters.AddWithValue("@DienTich", TextDienTich.Text)
            cmd.Parameters.AddWithValue("@LoaiDat", CmbLD.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@LoaiTaiLieu", CmbLoaiTaiLieu.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@XaID", CmbXa.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@HuyenID", CmbHuyen.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@PhongID", cmbPhong.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@NganID", CmbNgan.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@KeID", CmbKe.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@KhoID", CmbKho.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@MaBienDong", TextMaBienDong.Text)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub FrmSuaHoSoBienDong_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        NapLoaiTaiLieu()
        NapDonViHuyen()
        NapLoaiDat1()
        NapLoaiBienDong()
        NapPhong()
        NapNgan()
        NapKe()
        NapKho()
        NapTinhTrang()
        DataGridViewThuaDat.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewThuaDat.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        DataGridViewTenChu.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewTenChu.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
    End Sub

    Private Sub FrmSuaHoSoBienDong_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
        'If MsgBox("Bạn có muốn thoát khỏi chương trình không ?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Thoát chương trình") = MsgBoxResult.No Then
        '    e.Cancel = True
        'End If
    End Sub

    Private Sub ButtonTimKiem_Click(sender As Object, e As EventArgs) Handles ButtonTimKiem.Click
        Lamtuoi()
        NapDataGridView()
        NapDataGridViewThuaDat()
    End Sub

    Private Sub ButtonSuaTen_Click(sender As Object, e As EventArgs) Handles ButtonSuaTen.Click
        QL_HoSoSuaTen()
        Update_TenChu()
        NapDataGridView()
    End Sub

    Private Sub ButtonThuaDat_Click(sender As Object, e As EventArgs) Handles ButtonThuaDat.Click
        QL_HoSoSuaThua()
        Update_ThuaDat()
    End Sub

    Private Sub ButtonXoa_Click(sender As Object, e As EventArgs) Handles ButtonXoa.Click
        Try
            If TextTenChu.Text = "" Then
                MsgBox("Bạn phải chọn chủ sử dụng cần xóa")
            ElseIf MsgBox("Bạn có chắc muốn xóa không ?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                ChuyenTenChu()
                QL_XoaHoSoBienDong()
                XoaDuLieuTenChu()
                XoaDuLieuThuaDat()
                XoaDuLieuMucLucBienDong()
                Lamtuoi()
                NapDataGridView()
                NapDataGridViewThuaDat()
                MessageBox.Show(" Xóa thông tin thành công ! ")
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub ButtonThoat_Click(sender As Object, e As EventArgs) Handles ButtonThoat.Click
        Me.Close()
    End Sub

    Private Sub DataGridViewTenChu_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewTenChu.CellClick
        
        NapVaoTexBox()
    End Sub

    Private Sub DataGridViewThuaDat_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewThuaDat.CellClick
        
        NapVaoTexBoxThuaDat()
    End Sub

    
    Private Sub DataGridViewTenChu_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewTenChu.CellContentClick

    End Sub

    Private Sub DataGridViewTenChu_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewTenChu.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewTenChu.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewTenChu.RowHeadersWidth < Size.Width + 20 Then DataGridViewTenChu.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

    Private Sub DataGridViewThuaDat_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewThuaDat.CellContentClick

    End Sub

    Private Sub DataGridViewThuaDat_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewThuaDat.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewTenChu.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewTenChu.RowHeadersWidth < Size.Width + 20 Then DataGridViewTenChu.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

   
    Private Sub Them_thua_dat()
        Try
            ob_cnn.Open()
            Dim NgayNhap As String
            Dim Sql As String = ""
            Dim cmd As New SqlCommand
            Sql = "SELECT DISTINCT ngaynhap FROM THUADATBIENDONG WHERE SoHoSo = " + TextSoHoSo.Text + ""
            cmd = New SqlCommand(Sql, ob_cnn)
            Dim dt As New DataTable
            dt.Load(cmd.ExecuteReader())
            cmd.Dispose()
            NgayNhap = String.Format("{0:MM/dd/yyyy}", dt.Rows(0).Item(0).ToString())
            Sql = " insert into THUADATBIENDONG(SoHoSo, ThuaDat, ToBanDo, DienTich,LoaiDat,LoaiTaiLieu, " &
                " XaID, HuyenID, ngaynhap ) values (@SoHoSo, @ThuaDat, @ToBanDo, @DienTich, @LoaiDat, @LoaiTaiLieu, @XaID, @HuyenID, @NgayNhap)"
            cmd = New SqlCommand(Sql, ob_cnn)
            cmd.Parameters.AddWithValue("@SoHoSo", TextSoHoSo.Text)
            cmd.Parameters.AddWithValue("@ThuaDat", TextSoThua.Text)
            cmd.Parameters.AddWithValue("@ToBanDo", TextToBD.Text)
            cmd.Parameters.AddWithValue("@DienTich", TextDienTich.Text)
            cmd.Parameters.AddWithValue("@LoaiDat", CmbLD.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@LoaiTaiLieu", CmbLoaiTaiLieu.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@XaID", CmbXa.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@HuyenID", CmbHuyen.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@NgayNhap", DateTime.Parse(NgayNhap))
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub btnThem_Click(sender As Object, e As EventArgs) Handles btnThem.Click
        If TextTenChu.Text = "" Then
            MessageBox.Show("Tên chủ sử dụng không được rỗng")
            Return
        End If
        If TextDiaChi.Text = "" Then
            MessageBox.Show("Chưa nhập địa chỉ chủ sử dụng đất")
            Return
        End If
        If TextSoHoSo.Text = "" Then
            MessageBox.Show("Số hồ sơ không được rỗng")
            Return
        End If
        'If (checkID(TextSoHoSo.Text) > 0) Then
        '    MsgBox(" Đã trùng số hồ sơ, bạn kiểm tra lại ")
        '    'Exit Sub
        '    Return
        'End If

        If TextToBD.Text = "0" Or TextToBD.Text = "" Then
            MessageBox.Show("Chưa nhập thông tin tờ bản đồ")
            Return
        End If
        If TextToBD.Text < "0" Then
            MessageBox.Show("Tờ bản đồ không được nhỏ hơn 0")
            Return
        End If
        If TextSoThua.Text = "0" Or TextSoThua.Text = "" Then
            MessageBox.Show("Chưa nhập thông tin thửa đất")
            Return
        End If
        If TextSoThua.Text < "0" Then
            MessageBox.Show("Thửa đất phải lớn hơn 0 ")
            Return
        End If
        If TextDienTich.Text <= "0" Then
            MessageBox.Show(" Diện tích không được rỗng hoặc nhỏ hơn 0 ")
            Return
        Else
            Them_thua_dat()
            NapDataGridViewThuaDat()
            ob_cnn.Close()
        End If
    End Sub

    Private Sub XoaMotThuaDat()
        Dim sql As String
        sql = "Delete from THUADATBIENDONG where thuaID = '" & TextthuaID.Text & "' and SoHoSo = " & TextSoHoSo.Text & ""
        Dim cmd As New SqlCommand(sql, ob_cnn)
        ob_cnn.Open()
        cmd.ExecuteNonQuery()
        ob_cnn.Close()

    End Sub

    Private Sub btnXoaThua_Click(sender As Object, e As EventArgs) Handles btnXoaThua.Click
        If TextthuaID.Text = "" Then
            MessageBox.Show("Chọn thửa đất để xóa")
            Return
        End If
        If TextSoHoSo.Text = "" Then
            MessageBox.Show("Chọn thửa đất để xóa")
            Return
        End If
        Dim Msg As String
        Msg = MessageBox.Show("Bạn có thực sự muốn xóa không ?", "Lưu ý", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If Msg = Windows.Forms.DialogResult.Yes Then
            Chuyenthuadat()
            XoaMotThuaDat()
            TextthuaID.Text = ""
            NapDataGridViewThuaDat()
        End If
    End Sub
End Class